<?php
/**
 * The model file of report module of ZenTaoPMS.
 *
 * @copyright   Copyright 2009-2015 青岛易软天创网络科技有限公司(QingDao Nature Easy Soft Network Technology Co,LTD, www.cnezsoft.com)
 * @license     ZPL (http://zpl.pub/page/zplv12.html)
 * @author      Chunsheng Wang <chunsheng@cnezsoft.com>
 * @package     report
 * @version     $Id: model.php 4726 2013-05-03 05:51:27Z chencongzhi520@gmail.com $
 * @link        http://www.zentao.net
 */
?>
<?php

class reportModel extends model
{
    /**
     * Compute percent of every item.
     *
     * @param array $datas
     * @access public
     * @return array
     */
    public function computePercent($datas)
    {
        $sum = 0;
        foreach ($datas as $data) $sum += $data->value;

        $totalPercent = 0;
        foreach ($datas as $i => $data) {
            $data->percent = round($data->value / $sum, 4);
            $totalPercent += $data->percent;
        }
        if (isset($i)) $datas[$i]->percent = round(1 - $totalPercent + $datas[$i]->percent, 4);
        return $datas;
    }

    /**
     * Create json data of single charts
     * @param array $sets
     * @param array $dateList
     * @return string the json string
     */
    public function createSingleJSON($sets, $dateList)
    {
        $data = '[';
        $now = date('Y-m-d');
        $preValue = 0;
        $setsDate = array_keys($sets);
        foreach ($dateList as $i => $date) {
            $date = date('Y-m-d', strtotime($date));
            if ($date > $now) break;
            if (!isset($sets[$date]) and $sets) {
                $tmpDate = $setsDate;
                $tmpDate[] = $date;
                sort($tmpDate);
                $tmpDateStr = ',' . join(',', $tmpDate);
                $preDate = rtrim(substr($tmpDateStr, 0, strpos($tmpDateStr, $date)), ',');
                $preDate = substr($preDate, strrpos($preDate, ',') + 1);

                if ($preDate) {
                    $preValue = $sets[$preDate];
                    $preValue = $preValue->value;
                }
            }

            $data .= isset($sets[$date]) ? "{$sets[$date]->value}," : "{$preValue},";
        }
        $data = rtrim($data, ',');
        $data .= ']';
        return $data;
    }

    /**
     * Convert date format.
     *
     * @param array $dateList
     * @param string $format
     * @access public
     * @return array
     */
    public function convertFormat($dateList, $format = 'Y-m-d')
    {
        foreach ($dateList as $i => $date) $dateList[$i] = date($format, strtotime($date));
        return $dateList;
    }

    /**
     * Get projects.
     *
     * @access public
     * @return void
     */
    public function getProjects($begin = 0, $end = 0)
    {
        $tasks = $this->dao->select('t1.*,t2.name as projectName')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.status')->ne('cancel')
            ->andWhere('t1.deleted')->eq(0)
            ->beginIF(!$this->app->user->admin)->andWhere('t2.id')->in($this->app->user->view->projects)->fi()
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('t1.parent')->lt(1)
            ->andWhere('t2.status')->eq('closed')
            ->beginIF($begin)->andWhere('t2.begin')->ge($begin)->fi()
            ->beginIF($end)->andWhere('t2.end')->le($end)->fi()
            ->orderBy('t2.end_desc')
            ->fetchAll();

        $projects = array();
        foreach ($tasks as $task) {
            $projectID = $task->project;
            if (!isset($projects[$projectID])) {
                $projects[$projectID] = new stdclass();
                $projects[$projectID]->estimate = 0;
                $projects[$projectID]->consumed = 0;
            }

            $projects[$projectID]->name = $task->projectName;
            $projects[$projectID]->estimate += $task->estimate;
            $projects[$projectID]->consumed += $task->consumed;
        }

        return $projects;
    }

    /**
     * Get products.
     *
     * @access public
     * @return array
     */
    public function getProducts($conditions)
    {
        $products = $this->dao->select('id, code, name, PO')->from(TABLE_PRODUCT)
            ->where('deleted')->eq(0)
            ->beginIF(strpos($conditions, 'closedProduct') === false)->andWhere('status')->ne('closed')->fi()
            ->beginIF(!$this->app->user->admin)->andWhere('id')->in($this->app->user->view->products)->fi()
            ->fetchAll('id');
        $plans = $this->dao->select('*')->from(TABLE_PRODUCTPLAN)->where('deleted')->eq(0)->andWhere('product')->in(array_keys($products))
            ->beginIF(strpos($conditions, 'overduePlan') === false)->andWhere('end')->gt(date('Y-m-d'))->fi()
            ->fetchAll('id');
        foreach ($plans as $plan) $products[$plan->product]->plans[$plan->id] = $plan;

        $planStories = array();
        $unplannedStories = array();
        $stmt = $this->dao->select('id,plan,product,status')->from(TABLE_STORY)->where('deleted')->eq(0)->query();
        while ($story = $stmt->fetch()) {
            if (empty($story->plan)) {
                $unplannedStories[$story->id] = $story;
                continue;
            }

            $storyPlans = array();
            $storyPlans[] = $story->plan;
            if (strpos($story->plan, ',') !== false) $storyPlans = explode(',', trim($story->plan, ','));
            foreach ($storyPlans as $planID) {
                if (isset($plans[$planID])) {
                    $planStories[$story->id] = $story;
                    break;
                }
            }
        }

        foreach ($planStories as $story) {
            $storyPlans = array();
            $storyPlans[] = $story->plan;
            if (strpos($story->plan, ',') !== false) $storyPlans = explode(',', trim($story->plan, ','));
            foreach ($storyPlans as $planID) {
                if (!isset($plans[$planID])) continue;
                $plan = $plans[$planID];
                $products[$plan->product]->plans[$planID]->status[$story->status] = isset($products[$plan->product]->plans[$planID]->status[$story->status]) ? $products[$plan->product]->plans[$planID]->status[$story->status] + 1 : 1;
            }
        }

        foreach ($unplannedStories as $story) {
            $product = $story->product;
            if (isset($products[$product])) {
                if (!isset($products[$product]->plans[0])) {
                    $products[$product]->plans[0] = new stdClass();
                    $products[$product]->plans[0]->title = $this->lang->report->unplanned;
                    $products[$product]->plans[0]->begin = '';
                    $products[$product]->plans[0]->end = '';
                }
                $products[$product]->plans[0]->status[$story->status] = isset($products[$product]->plans[0]->status[$story->status]) ? $products[$product]->plans[0]->status[$story->status] + 1 : 1;
            }
        }

        unset($products['']);
        return $products;
    }

    /**
     * Get bugs
     *
     * @param int $begin
     * @param int $end
     * @access public
     * @return array
     */
    public function getBugs($begin, $end, $product, $project)
    {
        $end = date('Ymd', strtotime("$end +1 day"));
        $bugs = $this->dao->select('id, resolution, openedBy, status')->from(TABLE_BUG)
            ->where('deleted')->eq(0)
            ->andWhere('openedDate')->ge($begin)
            ->andWhere('openedDate')->le($end)
            ->beginIF($product)->andWhere('product')->eq($product)->fi()
            ->beginIF($project)->andWhere('project')->eq($project)->fi()
            ->fetchAll();

        $bugCreate = array();
        foreach ($bugs as $bug) {
            $bugCreate[$bug->openedBy][$bug->resolution] = empty($bugCreate[$bug->openedBy][$bug->resolution]) ? 1 : $bugCreate[$bug->openedBy][$bug->resolution] + 1;
            $bugCreate[$bug->openedBy]['all'] = empty($bugCreate[$bug->openedBy]['all']) ? 1 : $bugCreate[$bug->openedBy]['all'] + 1;
            if ($bug->status == 'resolved' or $bug->status == 'closed') {
                $bugCreate[$bug->openedBy]['resolved'] = empty($bugCreate[$bug->openedBy]['resolved']) ? 1 : $bugCreate[$bug->openedBy]['resolved'] + 1;
            }
        }

        foreach ($bugCreate as $account => $bug) {
            $validRate = 0;
            if (isset($bug['fixed'])) $validRate += $bug['fixed'];
            if (isset($bug['postponed'])) $validRate += $bug['postponed'];
            $bugCreate[$account]['validRate'] = (isset($bug['resolved']) and $bug['resolved']) ? ($validRate / $bug['resolved']) : "0";
        }
        uasort($bugCreate, 'sortSummary');
        return $bugCreate;
    }

    /**
     * Get workload.
     *
     * @param int $dept
     * @param string $assign
     *
     * @access public
     * @return array
     */
    public function getWorkload($dept = 0, $assign = 'assign')
    {
        $deptUsers = array();
        if ($dept) $deptUsers = $this->loadModel('dept')->getDeptUserPairs($dept);

        if ($assign == 'noassign') {
            $members = $this->dao->select('t1.account,t2.name,t1.root')->from(TABLE_TEAM)->alias('t1')
                ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t2.id = t1.root')
                ->where('t2.status')->notin('cancel, closed, done, suspended')
                ->beginIF($dept)->andWhere('t1.account')->in(array_keys($deptUsers))->fi()
                ->andWhere('t1.type')->eq('project')
                ->andWhere("t1.account NOT IN(SELECT `assignedTo` FROM " . TABLE_TASK . " WHERE `project` = t1.`root` AND `status` NOT IN('cancel, closed, done, pause') AND assignedTo != '' GROUP BY assignedTo)")
                ->fetchGroup('account', 'name');

            $workload = array();
            if (!empty($members)) {
                foreach ($members as $member => $projects) {
                    if (!empty($projects)) {
                        foreach ($projects as $name => $project) {
                            $workload[$member]['task'][$name]['count'] = 0;
                            $workload[$member]['task'][$name]['manhour'] = 0;
                            $workload[$member]['task'][$name]['projectID'] = $project->root;
                            $workload[$member]['total']['count'] = 0;
                            $workload[$member]['total']['manhour'] = 0;
                        }
                    }
                }
            }
            return $workload;
        }

        $stmt = $this->dao->select('t1.*, t2.name as projectName')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.deleted')->eq(0)
            ->andWhere('t1.status')->notin('cancel, closed, done, pause')
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('t2.status')->notin('cancel, closed, done, suspended')
            ->andWhere('assignedTo')->ne('');

        $allTasks = $stmt->fetchAll('id');
        $tasks = $stmt->beginIF($dept)->andWhere('t1.assignedTo')->in(array_keys($deptUsers))->fi()->fetchAll('id');

        if (empty($allTasks)) return array();

        /* Fix bug for children. */
        $parents = array();
        $taskIdList = array();
        $taskGroups = array();
        foreach ($tasks as $task) {
            if ($task->parent > 0) $parents[$task->parent] = $task->parent;
            $taskGroups[$task->assignedTo][$task->id] = $task;
        }

        $multiTaskTeams = $this->dao->select('*')->from(TABLE_TEAM)->where('type')->eq('task')
            ->andWhere('root')->in(array_keys($allTasks))
            ->beginIF($dept)->andWhere('account')->in(array_keys($deptUsers))->fi()
            ->fetchGroup('account', 'root');
        foreach ($multiTaskTeams as $assignedTo => $multiTasks) {
            foreach ($multiTasks as $task) {
                $userTask = clone $allTasks[$task->root];
                $userTask->estimate = $task->estimate;
                $userTask->consumed = $task->consumed;
                $userTask->left = $task->left;
                $taskGroups[$assignedTo][$task->root] = $userTask;
            }
        }

        $workload = array();
        foreach ($taskGroups as $user => $userTasks) {
            if ($user) {
                foreach ($userTasks as $task) {
                    if (isset($parents[$task->id])) continue;
                    $workload[$user]['task'][$task->projectName]['count'] = isset($workload[$user]['task'][$task->projectName]['count']) ? $workload[$user]['task'][$task->projectName]['count'] + 1 : 1;
                    $workload[$user]['task'][$task->projectName]['manhour'] = isset($workload[$user]['task'][$task->projectName]['manhour']) ? $workload[$user]['task'][$task->projectName]['manhour'] + $task->left : $task->left;
                    $workload[$user]['task'][$task->projectName]['projectID'] = $task->project;
                    $workload[$user]['total']['count'] = isset($workload[$user]['total']['count']) ? $workload[$user]['total']['count'] + 1 : 1;
                    $workload[$user]['total']['manhour'] = isset($workload[$user]['total']['manhour']) ? $workload[$user]['total']['manhour'] + $task->left : $task->left;
                }
            }
        }
        unset($workload['closed']);
        return $workload;
    }

    /**
     * Get bug assign.
     *
     * @access public
     * @return array
     */
    public function getBugAssign()
    {
        $bugs = $this->dao->select('t1.*, t2.name as productName')->from(TABLE_BUG)->alias('t1')
            ->leftJoin(TABLE_PRODUCT)->alias('t2')->on('t1.product = t2.id')
            ->where('t1.deleted')->eq(0)
            ->andWhere('t1.status')->eq('active')
            ->andWhere('t2.deleted')->eq(0)
            ->fetchGroup('assignedTo');
        $assign = array();
        foreach ($bugs as $user => $userBugs) {
            if ($user) {
                foreach ($userBugs as $bug) {
                    $assign[$user]['bug'][$bug->productName]['count'] = isset($assign[$user]['bug'][$bug->productName]['count']) ? $assign[$user]['bug'][$bug->productName]['count'] + 1 : 1;
                    $assign[$user]['bug'][$bug->productName]['productID'] = $bug->product;
                    $assign[$user]['total']['count'] = isset($assign[$user]['total']['count']) ? $assign[$user]['total']['count'] + 1 : 1;
                }
            }
        }
        unset($assign['closed']);
        return $assign;
    }

    /**
     * Get System URL.
     *
     * @access public
     * @return void
     */
    public function getSysURL()
    {
        if (isset($this->config->mail->domain)) return $this->config->mail->domain;

        /* Ger URL when run in shell. */
        if (PHP_SAPI == 'cli') {
            $url = parse_url(trim($this->server->argv[1]));
            $port = (empty($url['port']) or $url['port'] == 80) ? '' : $url['port'];
            $host = empty($port) ? $url['host'] : $url['host'] . ':' . $port;
            return $url['scheme'] . '://' . $host;
        } else {
            return common::getSysURL();
        }
    }

    /**
     * Get user bugs.
     *
     * @access public
     * @return void
     */
    public function getUserBugs()
    {
        return $this->dao->select('t1.id, t1.title, t2.account as user, t1.deadline')
            ->from(TABLE_BUG)->alias('t1')
            ->leftJoin(TABLE_USER)->alias('t2')
            ->on('t1.assignedTo = t2.account')
            ->where('t1.assignedTo')->ne('')
            ->andWhere('t1.assignedTo')->ne('closed')
            ->andWhere('t1.deleted')->eq(0)
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('t1.deadline', true)->eq('0000-00-00')
            ->orWhere('t1.deadline')->lt(date(DT_DATE1, strtotime('+4 day')))
            ->markRight(1)
            ->fetchGroup('user');
    }

    /**
     * Get user tasks.
     *
     * @access public
     * @return void
     */
    public function getUserTasks()
    {
        return $this->dao->select('t1.id, t1.name, t2.account as user, t1.deadline')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_USER)->alias('t2')->on('t1.assignedTo = t2.account')
            ->leftJoin(TABLE_PROJECT)->alias('t3')->on('t1.project = t3.id')
            ->where('t1.assignedTo')->ne('')
            ->andWhere('t1.deleted')->eq(0)
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('t1.status')->in('wait,doing')
            ->andWhere('t3.status')->ne('suspended')
            ->andWhere('t1.deadline', true)->eq('0000-00-00')
            ->orWhere('t1.deadline')->lt(date(DT_DATE1, strtotime('+4 day')))
            ->markRight(1)
            ->fetchGroup('user');
    }

    /**
     * Get user todos.
     *
     * @access public
     * @return array
     */
    public function getUserTodos()
    {
        $stmt = $this->dao->select('t1.*, t2.account as user')
            ->from(TABLE_TODO)->alias('t1')
            ->leftJoin(TABLE_USER)->alias('t2')
            ->on('t1.account = t2.account')
            ->where('t1.status')->eq('wait')
            ->orWhere('t1.status')->eq('doing')
            ->query();

        $todos = array();
        while ($todo = $stmt->fetch()) {
            if ($todo->type == 'task') $todo->name = $this->dao->findById($todo->idvalue)->from(TABLE_TASK)->fetch('name');
            if ($todo->type == 'bug') $todo->name = $this->dao->findById($todo->idvalue)->from(TABLE_BUG)->fetch('title');
            $todos[$todo->user][] = $todo;
        }
        return $todos;
    }

    /**
     * Get user testTasks.
     *
     * @access public
     * @return array
     */
    public function getUserTestTasks()
    {
        return $this->dao->select('t1.*, t2.account as user')->from(TABLE_TESTTASK)->alias('t1')
            ->leftJoin(TABLE_USER)->alias('t2')->on('t1.owner = t2.account')
            ->where('t1.deleted')->eq('0')
            ->andWhere('t2.deleted')->eq('0')
            ->andWhere("(t1.status='wait' OR t1.status='doing')")
            ->fetchGroup('user');
    }

    /**
     * @param int $begin
     * @param int $end
     * @param int $project
     * @return array 项目总耗时报表
     */
    public function getProjectSummary($begin = 0, $end = 0, $dept = 0, $project = 0)
    {

        $deptUsers = array();
        if ($dept) {
            $deptUsers = array_merge($this->loadModel('dept')->getDeptUserPairs($dept));
        }

        $stmt = $this->dao->select('t1.*, t2.name as projectName,t2.code')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.deleted')->eq(0)
            ->andWhere('t1.parent')->notin('-1')
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('finishedBy')->ne('');

        if ($begin) {
            $begin = $begin . ' ' . '00:00:00';
        }
        if ($end) {
            $end = $end . ' ' . '23:59:59';/*date('H:i:s', time());*/
        }

        $allTasks = $stmt->fetchAll('id');
        $tasks = $stmt->beginIF($begin)->andWhere('t1.finishedDate')->ge($begin)->fi()
            ->beginIF($end)->andWhere('t1.finishedDate')->le($end)->fi()
            ->beginIF($project)->andWhere('t1.project')->in($project)->fi()
            ->beginIF($dept)->andWhere('t1.finishedBy')->in(array_keys($deptUsers))->fi()
            ->orderBy('t1.finishedDate')->fetchAll('id');

        $projectSummary = array();
        foreach ($tasks as $task) {
            $projectID = $task->project;
            if (!isset($projectSummary[$projectID])) {
                $projectSummary[$projectID] = new stdclass();
                $projectSummary[$projectID]->estimate = 0;
                $projectSummary[$projectID]->consumed = 0;
            }

            $projectSummary[$projectID]->name = $task->projectName;
            $projectSummary[$projectID]->code = $task->code;
            $projectSummary[$projectID]->estimate += $task->estimate;
            $projectSummary[$projectID]->consumed += $task->consumed;
        }

        return $projectSummary;
    }

    /**
     * @param int $begin
     * @param int $end
     * @param int $project
     * @return array 项目人员分布表
     */
    public function getProjectUserSummary($begin = 0, $end = 0, $dept = 0, $project = 0)
    {

        $projectUsers = $this->dao->select('*')->from(TABLE_USER)->where('account')->ne('')->fetchAll();
        $userArray = array();
        foreach ($projectUsers as $id => $user) {
            $userArray[$user->account] = $user->realname;
        }
        $deptUsers = array();
        if ($dept) {
            $deptUsers = array_merge($this->loadModel('dept')->getDeptUserPairs($dept));
        }

        $stmt = $this->dao->select('t1.*, t2.name as projectName,t2.code')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.deleted')->eq(0)
            ->andWhere('t1.parent')->notin('-1')
            ->andWhere('t2.deleted')->eq(0)
            ->andWhere('finishedBy')->ne('');

        if ($begin) {
            $begin = $begin . ' ' . '00:00:00';
        }
        if ($end) {
            $end = $end . ' ' . '23:59:59';/*date('H:i:s', time());*/
        }

        $allTasks = $stmt->fetchAll('id');
        $tasks = $stmt
            ->beginIF($begin)->andWhere('t1.finishedDate')->ge($begin)->fi()
            ->beginIF($end)->andWhere('t1.finishedDate')->le($end)->fi()
            ->beginIF($project)->andWhere('t1.project')->in($project)->fi()
            ->beginIF($dept)->andWhere('t1.finishedBy')->in(array_keys($deptUsers))->fi()
            ->orderBy('t1.finishedDate')->fetchAll('id');

        $projectUserSummary = array();
        foreach ($tasks as $task) {
            $projectID = $task->project;
            if (!isset($projectUserSummary[$projectID]['user'][$task->finishedBy]['task'][$task->id]['consumed'])) {
                $projectUserSummary[$projectID]['user'][$task->finishedBy]['task'][$task->id]['consumed'] += $task->consumed;
                $projectUserSummary[$projectID]['user'][$task->finishedBy]['consumed'] += $task->consumed;
            }
            if (!isset($projectUserSummary[$projectID]['user'][$task->finishedBy]['finishedBy'])) {
                $projectUserSummary[$projectID]['user'][$task->finishedBy]['finishedBy'] = $userArray[$task->finishedBy];
            }
            $projectUserSummary[$projectID]['name'] = $task->projectName;
            $projectUserSummary[$projectID]['code'] = $task->code;
            $projectUserSummary[$projectID]['estimate'] += $task->estimate;
            $projectUserSummary[$projectID]['consumed'] += $task->consumed;
        }

        return $projectUserSummary;
    }

    public function writeProjectUserSummary($objPHPExcel, $index, $startRow, $projectUserSummary, $title, $begin, $end, $styleThinBlackBorderOutline, $styleFont)
    {
//        var_dump($index . ' - ' . $startRow . ' = ' . $title . ' * ' . $projectUserSummary);
        $styleBoldFont = array(
            'font' => array(
                'name' => 'Serif',
                'size' => 12,
                'bold' => TRUE,
                'italic' => FALSE,
                'underline' => PHPExcel_Style_Font::UNDERLINE_NONE,
                'strike' => FALSE,
                'color' => array(
                    'rgb' => '666666'
                )
            )
        );
        $totalUser = 0;
        $totalProject = 0;
        //Set properties 设置文件属性
        $objPHPExcel->getProperties()
            ->setCreator("张立强")
            ->setLastModifiedBy("张立强")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("亚康万玮-研发中心报表");

        $time = "";
        if ($begin != 0 && $end != 0) {
            $time = "(" . $begin . '到' . $end . ")";
        }

        $objPHPExcel->createSheet($index);
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Serif');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->setActiveSheetIndex($index)->setCellValue('A1', "亚康万玮——研发中心($title)\n" . $time . "项目人员分布");
//合并列
        $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
//表格标题赋值
        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A2', $this->lang->report->code)
            ->setCellValue('B2', $this->lang->report->projectName)
            ->setCellValue('C2', $this->lang->report->user)
            ->setCellValue('D2', $this->lang->report->myConsumed)
            ->setCellValue('E2', $this->lang->report->projectsTotal);
//设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);
//设置边框
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleThinBlackBorderOutline);
//设置表格字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(14)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setBold(true)->setSize(14)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true)->setSize(14)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true)->setSize(14)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true)->setSize(14)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
//设置字体纵向对齐方式
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $col = $startRow;
        foreach ($projectUserSummary as $id => $value) {
            $i = 1;
            foreach ($value['user'] as $finishedBy => $user) {
                if ($i == 1) {
                    $objPHPExcel->setActiveSheetIndex($index)
                        ->setCellValue('A' . $col, $value['code'])
                        ->setCellValue('B' . $col, $value['name'])
                        ->setCellValue('C' . $col, $user["finishedBy"])
                        ->setCellValue('D' . $col, $user['consumed'])
                        ->setCellValue('E' . $col, $value['consumed']);
                    $totalUser      += $user['consumed'];
                    $totalProject   += $value['consumed'];

                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $col . ':A' . (count($value['user']) + $col - $i));
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . (count($value['user']) + $col - $i))->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . (count($value['user']) + $col - $i))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                    $objPHPExcel->getActiveSheet()->mergeCells('B' . $col . ':B' . (count($value['user']) + $col - $i));
                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . (count($value['user']) + $col - $i))->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . (count($value['user']) + $col - $i))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                    $objPHPExcel->getActiveSheet()->mergeCells('E' . $col . ':E' . (count($value['user']) + $col - $i));
                    $objPHPExcel->getActiveSheet()->getStyle('E' . $col . ':E' . (count($value['user']) + $col - $i))->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('E' . $col . ':E' . (count($value['user']) + $col - $i))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                    $objPHPExcel->getActiveSheet()->getStyle('A' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . (count($value['user']) + $col - $i))->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . (count($value['user']) + $col - $i))->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('E' . $col . ':E' . (count($value['user']) + $col - $i))->applyFromArray($styleThinBlackBorderOutline);
                } else {
                    $objPHPExcel->setActiveSheetIndex($index)
                        ->setCellValue('C' . $col, $user["finishedBy"])
                        ->setCellValue('D' . $col, $user["consumed"]);
                    $totalUser      += $user['consumed'];

                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                }
                $objPHPExcel->getActiveSheet()->getRowDimension($col)->setRowHeight(20);
                $i++;
                $col++;
            }
        }

        $interval = array(0, 1, 2, 3, 4, 5);
        foreach ($interval as $key) {
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('B' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('C' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('D' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('E' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getRowDimension($col + $key)->setRowHeight(20);
            if ($key == 5) {
                $objPHPExcel->setActiveSheetIndex($index)
                    ->setCellValue('A' . ($col + $key), "合计")
                    ->setCellValue('D' . ($col + $key),$totalUser)
                    ->setCellValue('E' . ($col + $key),$totalProject);
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('D' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('E' . ($col + $key))->applyFromArray($styleBoldFont);

                $objPHPExcel->getActiveSheet()->getStyle('D' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('E' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
        }
        $rows = [6,7,8];
        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A' . ($col + $rows[0]), $title.'经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[1]), '研发二部经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[2]), '研发三部经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[3]), '研发四部经理审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 2]), 'CEO审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 1]), '财务部审批签字：');

        foreach ($rows as $key => $value) {
            $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[$key])->setRowHeight(30);
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[$key]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[$key]) . ':E' . ($col + $rows[$key]));
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[$key]) . ':E' . ($col + $rows[$key]))->applyFromArray($styleThinBlackBorderOutline);
        }

//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[0])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[1])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[2])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[3])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) - 2])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) - 1])->setRowHeight(30);
//
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[1]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[2]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[3]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 2]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 1]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[0]) . ':E' . ($col + $rows[0]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[1]) . ':E' . ($col + $rows[1]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[2]) . ':E' . ($col + $rows[2]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[3]) . ':E' . ($col + $rows[3]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 2]) . ':E' . ($col + $rows[sizeof($rows) - 2]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 1]) . ':E' . ($col + $rows[sizeof($rows) - 1]));
//
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]) . ':E' . ($col + $rows[0]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[1]) . ':E' . ($col + $rows[1]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[2]) . ':E' . ($col + $rows[2]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[3]) . ':E' . ($col + $rows[3]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 2]) . ':E' . ($col + $rows[sizeof($rows) - 2]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 1]) . ':E' . ($col + $rows[sizeof($rows) - 1]))->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->setTitle($title);
        return $objPHPExcel;
    }

    /**
     * Get taskSummary.
     *
     * @param int $dept
     * @param string $assign
     *
     * @access public
     * @return array
     */
    public function getTaskSummary($begin = 0, $end = 0, $user = 0, $dept = 0, $project = 0)
    {
        $deptUsers = array();
        if ($dept) {
            $deptUsers = array_merge($this->loadModel('dept')->getDeptUserPairs($dept));
        }

        $stmt = $this->dao->select('t1.*, t2.name as projectName,t2.code')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.deleted')->eq('0')
            ->andWhere('t1.parent')->notin('-1')
            ->andWhere('t2.deleted')->eq('0')
            ->andWhere('finishedBy')->ne('');

        if ($begin) {
            $begin = $begin . ' ' . '00:00:00';
        }
        if ($end) {
            $end = $end . ' ' . '23:59:59'; /* date('H:i:s', time()); */
        }
        $allTasks = $stmt->fetchAll('id');
        $tasks = $stmt->beginIF($dept)->andWhere('t1.finishedBy')->in(array_keys($deptUsers))->fi()
            ->beginIF($begin)->andWhere('t1.finishedDate')->ge($begin)->fi()
            ->beginIF($end)->andWhere('t1.finishedDate')->le($end)->fi()
            ->beginIF($project)->andWhere('t1.project')->in($project)->fi()
            ->beginIF($user)->andWhere('finishedBy')->in($user)->fi()->orderBy('t1.finishedDate')->fetchAll('id');

        if (empty($allTasks)) return array();

//        $depts = $this->loadModel('dept')->getOptionMenu();
//        foreach ($depts as $key => $dept) {
//            var_dump("部门ID:" . $key . " 长度" .mb_strlen($dept,'utf-8') . ' 部门名称: ' . mb_substr($dept,1,5,'utf-8') . "<br>");
//        }

        /* Fix bug for children. */
        $parents = array();
        $taskIdList = array();
        $taskGroups = array();
        foreach ($tasks as $task) {
            if ($task->parent > 0) $parents[$task->parent] = $task->parent;
            $taskGroups[$task->finishedBy][$task->id] = $task;
        }

//        $multiTaskTeams = $this->dao->select('*')->from(TABLE_TEAM)->where('type')->eq('task')
//            ->andWhere('root')->in(array_keys($allTasks))
//            ->beginIF($dept)->andWhere('account')->in(array_keys($deptUsers))->fi()
//            ->beginIF($user)->andWhere('account')->in($user)->fi()
//            ->fetchGroup('account', 'root');
//
//        foreach ($multiTaskTeams as $assignedTo => $multiTasks) {
//            foreach ($multiTasks as $task) {
//                $userTask = clone $allTasks[$task->root];
//                $userTask->estimate = $task->estimate;
//                $userTask->consumed = $task->consumed;
//                $userTask->left = $task->left;
//                $taskGroups[$assignedTo][$task->root] = $userTask;
//            }
//        }

        $taskSummary = array();
        foreach ($taskGroups as $user => $userTasks) {
            if ($user) {
                foreach ($userTasks as $task => $taskInfo) {
                    if (isset($parents[$task->id])) continue;

                    $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count'] = isset($taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count']) ? $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count'] + 1 : 1;
                    $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour'] = isset($taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour']) ? $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour'] + $taskInfo->consumed : $taskInfo->consumed;
                    $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['projectID'] = $taskInfo->project;
                    $taskSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['code'] = $taskInfo->code;

                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['id'] = $taskInfo->id;
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['taskName'] = $taskInfo->name;
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['pri'] = $taskInfo->pri;
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['estStarted'] = $taskInfo->estStarted == '0000-00-00' ? '-' : date_format(date_create($taskInfo->estStarted), 'y-m-d');
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['realStarted'] = $taskInfo->realStarted == '0000-00-00' ? '-' : date_format(date_create($taskInfo->realStarted), 'y-m-d');
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['deadline'] = $taskInfo->deadline == '0000-00-00' ? '-' : date_format(date_create($taskInfo->deadline), 'y-m-d');
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['finishedDate'] = $taskInfo->finishedDate;
                    $taskSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['estimate'] = $taskInfo->estimate;
                    $taskSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id] = isset($taskSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id]) ? $taskSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id] + 1 : 1;

                    $taskSummary[$taskInfo->finishedBy]['total']['count'] = isset($taskSummary[$taskInfo->finishedBy]['total']['count']) ? $taskSummary[$taskInfo->finishedBy]['total']['count'] + 1 : 1;
                    $taskSummary[$taskInfo->finishedBy]['total']['manhour'] = isset($taskSummary[$taskInfo->finishedBy]['total']['manhour']) ? $taskSummary[$taskInfo->finishedBy]['total']['manhour'] + $taskInfo->consumed : $taskInfo->consumed;

                    $taskSummary[$taskInfo->finishedBy]['manhour'] = isset($taskSummary[$taskInfo->finishedBy]['manhour']) ? $taskSummary[$taskInfo->finishedBy]['manhour'] + $taskInfo->consumed : $taskInfo->consumed;
                }
            }
        }
        unset($taskSummary['closed']);
        return $taskSummary;
    }

    public function writeTaskSummaryExcel($objPHPExcel, $index, $startRow, $taskSummarys, $title, $begin, $end, $styleThinBlackBorderOutline, $styleFont) {
//        var_dump($index, $startRow, $title);
        $styleBoldFont = array(
            'name' => 'Serif',
            'size' => 12,
            'bold' => TRUE,
            'italic' => FALSE,
            'underline' => PHPExcel_Style_Font::UNDERLINE_NONE,
            'strike' => FALSE,
            'color' => array(
                'rgb' => '666666'
            )
        );

        $totalPreTime = 0;
        $totalTasks = 0;
        $totalTaskTime = 0;
        $totalUserTime = 0;
        $users = $this->loadModel('user')->getPairs('noletter|noclosed|nodeleted');

        $objPHPExcel->getProperties()
            ->setCreator("张立强")
            ->setLastModifiedBy("张立强")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("亚康万玮-研发中心报表");

        $objPHPExcel->createSheet();
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Serif');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $time = "";
        if ($begin != 0 && $end != 0) {
            $time = "(" . $begin . '到' . $end . ")";
        }

        $objPHPExcel->setActiveSheetIndex($index)->setCellValue('A1', "亚康万玮——研发中心($title)\n" . $time . "任务完成汇总表");
        $objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('A1:M1')->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);

        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A2', $this->lang->report->finishedByAB)
            ->setCellValue('B2', $this->lang->report->code)
            ->setCellValue('C2', $this->lang->report->projectName)
            ->setCellValue('D2', $this->lang->report->id)
            ->setCellValue('E2', $this->lang->report->taskName)
            ->setCellValue('F2', $this->lang->report->estStarted)
            ->setCellValue('G2', $this->lang->report->realStarted)
            ->setCellValue('H2', $this->lang->report->deadline)
            ->setCellValue('I2', $this->lang->report->finishedDate)
            ->setCellValue('J2', $this->lang->report->taskEstimate)
            ->setCellValue('K2', $this->lang->report->tasksTotal)
            ->setCellValue('L2', $this->lang->report->taskConsumed)
            ->setCellValue('M2', $this->lang->report->myConsumed);

        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $col = $startRow;
        foreach ($taskSummarys as $account => $load) {
            $hang = count($load['taskSum']);
            $id = 1;
            foreach ($load['projects'] as $project => $info) {
                $taskCount = $info['count'];
                $tid = 1;
                foreach ($load['tasks'] as $key => $projectTask) {
                    if ($project == $key) {
                        foreach ($projectTask as $pName => $taskInfo) {
                            $objPHPExcel->getActiveSheet()->getRowDimension($col)->setRowHeight(28);
                            if ($id == 1 && $tid == 1) {
                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('A' . $col, $users[$account])
                                    ->setCellValue('M' . $col, $load['manhour']);

                                $totalUserTime  += $load['manhour'];

                                $objPHPExcel->getActiveSheet()->mergeCells('A' . $col . ':A' . ($hang + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('M' . $col . ':M' . ($hang + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($hang + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($hang + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($hang + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($hang + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('B' . ($col), $info['code'])
                                    ->setCellValue('C' . ($col), $project)
                                    ->setCellValue('K' . ($col), $load['total']['count'])
                                    ->setCellValue('L' . ($col), $info['manhour']);

                                $totalTasks     += $load['total']['count'];
                                $totalTaskTime  += $info['manhour'];

                                $objPHPExcel->getActiveSheet()->mergeCells('B' . $col . ':B' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('C' . $col . ':C' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('K' . $col . ':K' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('L' . $col . ':L' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskInfo['estStarted'])
                                    ->setCellValue('G' . ($col), $taskInfo['realStarted'])
                                    ->setCellValue('H' . ($col), $taskInfo['deadline'])
                                    ->setCellValue('I' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('J' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            } else if ($id == 1 && $tid != 1) {
                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskInfo['estStarted'])
                                    ->setCellValue('G' . ($col), $taskInfo['realStarted'])
                                    ->setCellValue('H' . ($col), $taskInfo['deadline'])
                                    ->setCellValue('I' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('J' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            } else if ($id != 1) {
                                if ($tid == 1) {
                                    $objPHPExcel->setActiveSheetIndex($index)
                                        ->setCellValue('B' . ($col), $info['code'])
                                        ->setCellValue('C' . ($col), $project)
                                        ->setCellValue('K' . ($col), $load['total']['count'])
                                        ->setCellValue('L' . ($col), $info['manhour']);

                                    $totalTasks     += $load['total']['count'];
                                    $totalTaskTime  += $info['manhour'];

                                    $objPHPExcel->getActiveSheet()->mergeCells('B' . $col . ':B' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('C' . $col . ':C' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('K' . $col . ':K' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                    $objPHPExcel->getActiveSheet()->getStyle('K' . $col . ':K' . ($taskCount + $col - $tid))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('L' . $col . ':L' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                    $objPHPExcel->getActiveSheet()->getStyle('L' . $col . ':L' . ($taskCount + $col - $tid))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                                }

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskInfo['estStarted'])
                                    ->setCellValue('G' . ($col), $taskInfo['realStarted'])
                                    ->setCellValue('H' . ($col), $taskInfo['deadline'])
                                    ->setCellValue('I' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('J' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            }
                            $tid++;
                            $col++;
                        }
                    }
                }
                $id++;
            }
        }

        $interval = array(0, 1, 2, 3, 4, 5);
        foreach ($interval as $key) {
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('B' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('C' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('D' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('E' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('F' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('G' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('H' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('I' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('J' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('K' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getRowDimension($col + $key)->setRowHeight(20);

            if ($key == 5) {
                $objPHPExcel->setActiveSheetIndex($index)
                    ->setCellValue('A' . ($col + $key),'合计')
                    ->setCellValue('J' . ($col + $key),$totalPreTime)
                    ->setCellValue('K' . ($col + $key),$totalTasks)
                    ->setCellValue('L' . ($col + $key),$totalTaskTime)
                    ->setCellValue('M' . ($col + $key),$totalUserTime);

                $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('J' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('K' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->applyFromArray($styleBoldFont);

                $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('J' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('K' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->getFont()->setBold(true);

                $objPHPExcel->getActiveSheet()->getStyle('J' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('K' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
        }
        $rows = [6,7,8];
        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A' . ($col + $rows[0]), $title.'经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[1]), '研发二部经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[2]), '研发三部经理审批签字：')
//            ->setCellValue('A' . ($col + $rows[3]), '研发四部经理审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 2]), 'CEO审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 1]), '财务部审批签字：');

        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[0])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[1])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[2])->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[3])->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) -2])->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) -1])->setRowHeight(30);

        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[1]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[2]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[3]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows)-2]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows)-1]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[0]) . ':M' . ($col + $rows[0]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[1]) . ':M' . ($col + $rows[1]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[2]) . ':M' . ($col + $rows[2]));
//        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[3]) . ':M' . ($col + $rows[3]));
        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 2]) . ':M' . ($col + $rows[sizeof($rows) - 2]));
        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 1]) . ':M' . ($col + $rows[sizeof($rows) - 1]));

        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]) . ':M' . ($col + $rows[0]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[1]) . ':M' . ($col + $rows[1]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[2]) . ':M' . ($col + $rows[2]))->applyFromArray($styleThinBlackBorderOutline);
//        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[3]) . ':M' . ($col + $rows[3]))->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 2]) . ':M' . ($col + $rows[sizeof($rows) - 2]))->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[count($rows) - 1]) . ':M' . ($col + $rows[count($rows) - 1]))->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->setTitle($title);

        return $objPHPExcel;
    }

    /**
     * Get taskSummaryTracking.
     *
     * @param int $dept
     * @param string $assign
     *
     * @access public
     * @return array
     */
    public function getTaskTrackingSummary($begin = 0, $end = 0, $user = 0, $dept = 0, $project = 0)
    {
        $deptUsers = array();
        if ($dept) {
            $deptUsers = array_merge($this->loadModel('dept')->getDeptUserPairs($dept));
        }
        
        $stmt = $this->dao->select('t1.*, t2.name as projectName,t2.code')->from(TABLE_TASK)->alias('t1')
            ->leftJoin(TABLE_PROJECT)->alias('t2')->on('t1.project = t2.id')
            ->where('t1.deleted')->eq('0')
            ->andWhere('t1.parent')->notin('-1')
            ->andWhere('t2.deleted')->eq('0');

//        var_dump($stmt);

        if ($begin) {
            $begin = $begin . ' ' . '00:00:00';
        }
        if ($end) {
            $end = $end . ' ' . '23:59:59'; /* date('H:i:s', time()); */
        }
        $allTasks = $stmt->fetchAll('id');
        $tasks = $stmt->beginIF($dept)->andWhere('t1.openedBy')->in(array_keys($deptUsers))->fi()
            ->beginIF($begin)->andWhere('t1.openedDate')->ge($begin)->fi()
            ->beginIF($end)->andWhere('t1.openedDate')->le($end)->fi()
            ->beginIF($project)->andWhere('t1.project')->in($project)->fi()
            ->beginIF($user)->andWhere('openedBy')->in($user)->fi()->orderBy('t1.openedDate')->fetchAll('id');

        if (empty($allTasks)) return array();

        /* Fix bug for children. */
        $parents = array();
        $taskIdList = array();
        $taskGroups = array();
        foreach ($tasks as $task) {
            if ($task->parent > 0) $parents[$task->parent] = $task->parent;
            $taskGroups[$task->openedBy][$task->id] = $task;
        }

        $taskTrackingSummary = array();
        foreach ($taskGroups as $user => $userTasks) {
            if ($user) {
                foreach ($userTasks as $task => $taskInfo) {
                    if (isset($parents[$task->id])) continue;

                    $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count'] = isset($taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count']) ? $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['count'] + 1 : 1;
                    $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour'] = isset($taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour']) ? $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['manhour'] + $taskInfo->consumed : $taskInfo->consumed;
                    $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['projectID'] = $taskInfo->project;
                    $taskTrackingSummary[$taskInfo->finishedBy]['projects'][$taskInfo->projectName]['code'] = $taskInfo->code;

                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['id'] = $taskInfo->id;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['taskName'] = $taskInfo->name;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['pri'] = $taskInfo->pri;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['type'] = $taskInfo->type;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['status'] = $taskInfo->status;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['openedBy'] = $taskInfo->openedBy;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['openedDate'] = $taskInfo->openedDate == '0000-00-00' ? '-' : date_format(date_create($taskInfo->openedDate), 'y-m-d');
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['estStarted'] = $taskInfo->estStarted == '0000-00-00' ? '-' : date_format(date_create($taskInfo->estStarted), 'y-m-d');
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['realStarted'] = $taskInfo->realStarted == '0000-00-00' ? '-' : date_format(date_create($taskInfo->realStarted), 'y-m-d');
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['deadline'] = $taskInfo->deadline == '0000-00-00' ? '-' : date_format(date_create($taskInfo->deadline), 'y-m-d');
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['finishedDate'] = $taskInfo->finishedDate;
                    $taskTrackingSummary[$taskInfo->finishedBy]['tasks'][$taskInfo->projectName][$taskInfo->id]['estimate'] = $taskInfo->estimate;
                    $taskTrackingSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id] = isset($taskTrackingSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id]) ? $taskTrackingSummary[$taskInfo->finishedBy]['taskSum'][$taskInfo->id] + 1 : 1;

                    $taskTrackingSummary[$taskInfo->finishedBy]['total']['count'] = isset($taskTrackingSummary[$taskInfo->finishedBy]['total']['count']) ? $taskTrackingSummary[$taskInfo->finishedBy]['total']['count'] + 1 : 1;
                    $taskTrackingSummary[$taskInfo->finishedBy]['total']['manhour'] = isset($taskTrackingSummary[$taskInfo->finishedBy]['total']['manhour']) ? $taskTrackingSummary[$taskInfo->finishedBy]['total']['manhour'] + $taskInfo->consumed : $taskInfo->consumed;

                    $taskTrackingSummary[$taskInfo->finishedBy]['manhour'] = isset($taskTrackingSummary[$taskInfo->finishedBy]['manhour']) ? $taskTrackingSummary[$taskInfo->finishedBy]['manhour'] + $taskInfo->consumed : $taskInfo->consumed;
                }
            }
        }
        unset($taskTrackingSummary['closed']);
        return $taskTrackingSummary;
    }
    public function writeTaskTrackingSummaryExcel($objPHPExcel, $index, $startRow, $taskSummarys, $title, $begin, $end, $styleThinBlackBorderOutline, $styleFont,$taskPri,$taskType,$taskStatus) {
//        var_dump($index, $startRow, $title);
        $styleBoldFont = array(
            'name' => 'Serif',
            'size' => 12,
            'bold' => TRUE,
            'italic' => FALSE,
            'underline' => PHPExcel_Style_Font::UNDERLINE_NONE,
            'strike' => FALSE,
            'color' => array(
                'rgb' => '666666'
            )
        );

        $totalPreTime = 0;
        $totalTasks = 0;
        $totalTaskTime = 0;
        $totalUserTime = 0;
        $users = $this->loadModel('user')->getPairs('noletter|noclosed|nodeleted');


        $objPHPExcel->getProperties()
            ->setCreator("张立强")
            ->setLastModifiedBy("张立强")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("亚康万玮-研发中心报表");

        $objPHPExcel->createSheet();
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Serif');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $time = "";
        if ($begin != 0 && $end != 0) {
            $time = "(" . $begin . '到' . $end . ")";
        }

        $objPHPExcel->setActiveSheetIndex($index)->setCellValue('A1', "亚康万玮——研发中心($title)\n" . $time . "任务跟踪汇总表");
        $objPHPExcel->getActiveSheet()->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('A1:O1')->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(12);

        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A2', $this->lang->report->finishedByAB)
            ->setCellValue('B2', $this->lang->report->code)
            ->setCellValue('C2', $this->lang->report->projectName)
            ->setCellValue('D2', $this->lang->report->id)
            ->setCellValue('E2', $this->lang->report->taskName)

            ->setCellValue('F2', $this->lang->report->pri)
            ->setCellValue('G2', $this->lang->report->type)
            ->setCellValue('H2', $this->lang->report->status)
            ->setCellValue('I2', $this->lang->report->openedBy)
            ->setCellValue('J2', $this->lang->report->openedDate)
            ->setCellValue('K2', $this->lang->report->finishedDate)

            ->setCellValue('L2', $this->lang->report->taskEstimate)
            ->setCellValue('M2', $this->lang->report->tasksTotal)
            ->setCellValue('N2', $this->lang->report->taskConsumed)
            ->setCellValue('O2', $this->lang->report->myConsumed);

        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('O2')->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('N2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
        $objPHPExcel->getActiveSheet()->getStyle('O2')->getFont()->setBold(true)->setSize(12)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('J2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('L2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('M2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('N2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('O2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $col = $startRow;
        foreach ($taskSummarys as $account => $load) {
            $hang = count($load['taskSum']);
            $id = 1;
            foreach ($load['projects'] as $project => $info) {
                $taskCount = $info['count'];
                $tid = 1;
                foreach ($load['tasks'] as $key => $projectTask) {
                    if ($project == $key) {
                        foreach ($projectTask as $pName => $taskInfo) {
                            $objPHPExcel->getActiveSheet()->getRowDimension($col)->setRowHeight(28);
                            if ($id == 1 && $tid == 1) {
                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('A' . $col, $users[$account])
                                    ->setCellValue('O' . $col, $load['manhour']);

                                $totalUserTime  += $load['manhour'];

                                $objPHPExcel->getActiveSheet()->mergeCells('A' . $col . ':A' . ($hang + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('A' . $col . ':A' . ($hang + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('O' . $col . ':O' . ($hang + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('O' . $col . ':O' . ($hang + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('O' . $col . ':O' . ($hang + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('O' . $col . ':O' . ($hang + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('O' . $col . ':O' . ($hang + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('B' . ($col), $info['code'])
                                    ->setCellValue('C' . ($col), $project)
                                    ->setCellValue('M' . ($col), $info['count'])
                                    ->setCellValue('N' . ($col), $info['manhour']);

                                $totalTasks     += $info['count'];
                                $totalTaskTime  += $info['manhour'];

                                $objPHPExcel->getActiveSheet()->mergeCells('B' . $col . ':B' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('C' . $col . ':C' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('M' . $col . ':M' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->getActiveSheet()->mergeCells('N' . $col . ':N' . ($taskCount + $col - $id));
                                $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $id))->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $id))->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $id))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $id))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskPri[$taskInfo['pri']])
                                    ->setCellValue('G' . ($col), $taskType[$taskInfo['type']])
                                    ->setCellValue('H' . ($col), $taskStatus[$taskInfo['status']])
                                    ->setCellValue('I' . ($col), $users[$taskInfo['openedBy']])
                                    ->setCellValue('J' . ($col), $taskInfo['openedDate'])
                                    ->setCellValue('K' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('L' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            } else if ($id == 1 && $tid != 1) {
                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskPri[$taskInfo['pri']])
                                    ->setCellValue('G' . ($col), $taskType[$taskInfo['type']])
                                    ->setCellValue('H' . ($col), $taskStatus[$taskInfo['status']])
                                    ->setCellValue('I' . ($col), $users[$taskInfo['openedBy']])
                                    ->setCellValue('J' . ($col), $taskInfo['openedDate'])
                                    ->setCellValue('K' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('L' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            } else if ($id != 1) {
                                if ($tid == 1) {
                                    $objPHPExcel->setActiveSheetIndex($index)
                                        ->setCellValue('B' . ($col), $info['code'])
                                        ->setCellValue('C' . ($col), $project)
                                        ->setCellValue('M' . ($col), $info['count'])
                                        ->setCellValue('N' . ($col), $info['manhour']);

                                    $totalTasks     += $info['count'];
                                    $totalTaskTime  += $info['manhour'];

                                    $objPHPExcel->getActiveSheet()->mergeCells('B' . $col . ':B' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('B' . $col . ':B' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('C' . $col . ':C' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('C' . $col . ':C' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('M' . $col . ':M' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                                    $objPHPExcel->getActiveSheet()->mergeCells('N' . $col . ':N' . ($taskCount + $col - $tid));
                                    $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $tid))->applyFromArray($styleThinBlackBorderOutline);
                                    $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $tid))->applyFromArray($styleFont);
                                    $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $tid))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                                    $objPHPExcel->getActiveSheet()->getStyle('N' . $col . ':N' . ($taskCount + $col - $tid))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                                    $objPHPExcel->getActiveSheet()->getStyle('M' . $col . ':M' . ($taskCount + $col - $tid))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                                }

                                $objPHPExcel->setActiveSheetIndex($index)
                                    ->setCellValue('D' . ($col), $taskInfo['id'])
                                    ->setCellValue('E' . ($col), $taskInfo['taskName'])
                                    ->setCellValue('F' . ($col), $taskPri[$taskInfo['pri']])
                                    ->setCellValue('G' . ($col), $taskType[$taskInfo['type']])
                                    ->setCellValue('H' . ($col), $taskStatus[$taskInfo['status']])
                                    ->setCellValue('I' . ($col), $users[$taskInfo['openedBy']])
                                    ->setCellValue('J' . ($col), $taskInfo['openedDate'])
                                    ->setCellValue('K' . ($col), $taskInfo['finishedDate'])
                                    ->setCellValue('L' . ($col), $taskInfo['estimate']);

                                $totalPreTime += $taskInfo['estimate'];

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleThinBlackBorderOutline);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleThinBlackBorderOutline);

                                $objPHPExcel->getActiveSheet()->getStyle('D' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('E' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('F' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('G' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('H' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('I' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('K' . $col)->applyFromArray($styleFont);
                                $objPHPExcel->getActiveSheet()->getStyle('L' . $col)->applyFromArray($styleFont);

                                $objPHPExcel->getActiveSheet()->getStyle('J' . $col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                            }
                            $tid++;
                            $col++;
                        }
                    }
                }
                $id++;
            }
        }

        $interval = array(0, 1, 2, 3, 4, 5);
        foreach ($interval as $key) {
            $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('B' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('C' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('D' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('E' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('F' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('G' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('H' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('I' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('J' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('K' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('N' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getStyle('O' . ($col + $key))->applyFromArray($styleThinBlackBorderOutline);
            $objPHPExcel->getActiveSheet()->getRowDimension($col + $key)->setRowHeight(20);

            if ($key == 5) {
                $objPHPExcel->setActiveSheetIndex($index)
                    ->setCellValue('A' . ($col + $key),'合计')
                    ->setCellValue('L' . ($col + $key),$totalPreTime)
                    ->setCellValue('M' . ($col + $key),$totalTasks)
                    ->setCellValue('N' . ($col + $key),$totalTaskTime)
                    ->setCellValue('O' . ($col + $key),$totalUserTime);

                $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('N' . ($col + $key))->applyFromArray($styleBoldFont);
                $objPHPExcel->getActiveSheet()->getStyle('O' . ($col + $key))->applyFromArray($styleBoldFont);

                $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('N' . ($col + $key))->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('O' . ($col + $key))->getFont()->setBold(true);

                $objPHPExcel->getActiveSheet()->getStyle('L' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('M' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('N' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('O' . ($col + $key))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }
        }
        $rows = [6,7,8];
        $objPHPExcel->setActiveSheetIndex($index)
            ->setCellValue('A' . ($col + $rows[0]), $title.'经理审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 2]), 'CEO审批签字：')
            ->setCellValue('A' . ($col + $rows[sizeof($rows) - 1]), '财务部审批签字：');

        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[0])->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) -2])->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension($col + $rows[sizeof($rows) -1])->setRowHeight(30);

        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows)-2]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows)-1]))->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[0]) . ':O' . ($col + $rows[0]));
        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 2]) . ':O' . ($col + $rows[sizeof($rows) - 2]));
        $objPHPExcel->getActiveSheet()->mergeCells('A' . ($col + $rows[sizeof($rows) - 1]) . ':O' . ($col + $rows[sizeof($rows) - 1]));

        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[0]) . ':O' . ($col + $rows[0]))->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[sizeof($rows) - 2]) . ':O' . ($col + $rows[sizeof($rows) - 2]))->applyFromArray($styleThinBlackBorderOutline);
        $objPHPExcel->getActiveSheet()->getStyle('A' . ($col + $rows[count($rows) - 1]) . ':O' . ($col + $rows[count($rows) - 1]))->applyFromArray($styleThinBlackBorderOutline);

        $objPHPExcel->getActiveSheet()->setTitle($title);

        return $objPHPExcel;
    }
}

/**
 * @param $pre
 * @param $next
 *
 * @return int
 */
function sortSummary($pre, $next)
{
    if ($pre['validRate'] == $next['validRate']) return 0;
    return $pre['validRate'] > $next['validRate'] ? -1 : 1;
}
